﻿#region Librerias

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using ESF.General;
using System.Xml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

#endregion


namespace ESF.MSExcel
{
	public class XlsWriter : IDisposable
	{
		#region Miembros

		private bool _UseBasicExcel;

		private bool _Closed;

		private Columns _CurrentCols;

		private SpreadsheetDocument _Doc;

		private Workbook _Workbook;

		private WorkbookPart _WorkBookPart;

		private WorksheetPart _WorkSheetPart;

		private Worksheet _CurrentWorkSheet;

		private Sheet _CurrentSheet;

		private Row _CurrentRow;

		private SheetData _CurrentSheetData;

		private Hyperlinks _CurrentHyperLinks;

		private int _LinkIndex;

		//private int _WorkSheetIndex;

		private uint _SheetIndex;

		//private int _WorkbookIndex;

		private uint _RowIndex;

		private int _ColIndex;

		#endregion


		#region Constructor

		public XlsWriter(string pFileName, bool pUseBasicExcel = false)
		{
			ESF.General.FileHelper.Delete(pFileName);

			_Doc = SpreadsheetDocument.Create(pFileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, true);

			_LinkIndex = 0;

			_RowIndex = 0;

			_SheetIndex = 0;

			_UseBasicExcel = pUseBasicExcel;

			//_WorkbookIndex = 0;

			//_WorkSheetIndex = 0;

			_WorkBookPart = _Doc.AddWorkbookPart();

			_Workbook = new Workbook();
		}

		#endregion


		#region Funciones

		public void AddSheet(string pName)
		{
			_SheetIndex++;
			_CurrentSheet = new Sheet() { Name = pName, SheetId = _SheetIndex, Id = "s" + _SheetIndex };

			//_WorkSheetIndex ++;
			_WorkSheetPart = _WorkBookPart.AddNewPart<WorksheetPart>(_CurrentSheet.Id);


			_CurrentWorkSheet = new Worksheet();

			_CurrentWorkSheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

			_CurrentSheetData = new SheetData();

			_CurrentHyperLinks = new Hyperlinks();

			_CurrentCols = new Columns();
		}


		public void StartRow()
		{
			_ColIndex = 0;

			_RowIndex++;

			_CurrentRow = new Row() { RowIndex = _RowIndex };
		}


		public void StartRow(byte pOutLineLevel)
		{
			_ColIndex = 0;

			_RowIndex++;

			if (!_UseBasicExcel)
			{
				_CurrentRow = new Row() { RowIndex = _RowIndex, OutlineLevel = pOutLineLevel, DyDescent = 0.25D };
			}
			else
			{
				_CurrentRow = new Row();
			}
		}


		#region Columns

		public void AddColumn(string pValue, int pWidth)
		{
			AddColumn(pValue, pWidth, 1);
		}


		public void AddColumn(string pValue, int pWidth, uint pStyle)
		{
			AddCell(pValue);

			_CurrentCols.Append(new Column() { Width = pWidth, BestFit = true, CustomWidth = true, Min = (UInt32Value)2U, Max = (UInt32Value)2U });
		}

		#endregion


		#region Add Cell

		public Cell AddEmptyCell()
		{
			return AddCell (string.Empty);
		}


		public Cell AddCell(string pValue)
		{
			return AddCell(pValue, CellValues.String);
		}


		public Cell AddCell(string pValue, CellValues pDataType)
		{
			Cell newCell;


			_ColIndex++;

			newCell = new Cell { CellValue = new CellValue(string.IsNullOrEmpty(pValue) ? string.Empty : pValue), DataType = pDataType };

			_CurrentRow.AppendChild(newCell);

			return newCell;
		}


		public Cell AddCell(bool? pValue)
		{
			if (pValue == null)
			{
				return AddCell(null, CellValues.Boolean);
			}

			return AddCell(pValue.ToString(), CellValues.Boolean);
		}


		public Cell AddCell(DateTime? pValue)
		{
			if (pValue == null)
			{
				return AddCell(null, CellValues.Date);
			}

			return AddCell(pValue.Value.ToString("yyyy-MM-dd HH:mm:ss tt"), CellValues.String);
		}


		public Cell AddCell(double? pValue)
		{
			if (pValue == null)
			{
				return AddCell(null, CellValues.Number);
			}

			return AddCell(string.Format("{0:0.##}", pValue), CellValues.Number);
		}


		public Cell AddCell(int? pValue)
		{
			if (pValue == null)
			{
				return AddCell(null, CellValues.Number);
			}

			return AddCell(string.Format("{0}", pValue), CellValues.Number);
		}


		public Cell AddCell(long? pValue)
		{
			if (pValue == null)
			{
				return AddCell(null, CellValues.Number);
			}

			return AddCell(string.Format("{0}", pValue), CellValues.Number);
		}


		public Cell AddCell(float? pValue)
		{
			if (pValue == null)
			{
				return AddCell(null, CellValues.Number);
			}

			return AddCell(string.Format("{0}", pValue), CellValues.Number);
		}

		#endregion


		#region Hyperlink

		public Cell AddHyperLinkCell(string pValue, Uri pUri)
		{
			Hyperlink link;

			string lnkId;

			Cell newCell;


			newCell = AddCell(pValue);


			if (_UseBasicExcel) { return newCell; }


			_LinkIndex++;
			lnkId = "lnk" + _LinkIndex;

			_WorkSheetPart.AddHyperlinkRelationship(pUri, true, lnkId);

			link = new Hyperlink() { Reference = TextHelper.GetAlphaExcel(_ColIndex) + _RowIndex, Id = lnkId };

			_CurrentHyperLinks.Append(link);

			return newCell;
		}

		#endregion


		public void CloseRow()
		{
			_CurrentSheetData.Append(_CurrentRow);
		}


		public void CloseSheet()
		{
			CloseSheet (true);
		}


		public void CloseSheet (bool pSummaryBelow)
		{
			SheetProperties sheetProperties = new SheetProperties(); 
			
			
			_Workbook.Append(new Sheets(_CurrentSheet));

			_WorkBookPart.Workbook = _Workbook;

			if (!_UseBasicExcel)
			{
				sheetProperties.Append (new OutlineProperties() { SummaryBelow = pSummaryBelow });
			}

			_CurrentWorkSheet.Append (sheetProperties);
			_CurrentWorkSheet.Append (new SheetFormatProperties () { DefaultRowHeight = 15D, OutlineLevelRow = 0, DyDescent = 0.25D });

			if (_CurrentCols.Count() > 0)
			{
				_CurrentWorkSheet.Append(_CurrentCols);
			}

			_CurrentWorkSheet.Append(_CurrentSheetData);

			if (_CurrentHyperLinks.Count() > 0)
			{
				_CurrentWorkSheet.Append(_CurrentHyperLinks);
			}

			_WorkSheetPart.Worksheet = _CurrentWorkSheet;
		}


		public void Close()
		{
			if (!_Closed)
			{
				_Closed = true;

				_Doc.Close();
			}
		}


		#endregion


		public void Dispose()
		{
			Close();
		}
	}
}
